import * as sqlite3 from 'sqlite3';
import { parse } from 'path'
import { existsSync, mkdirSync } from 'fs';

type TableValueType = 'string' | 'number' | 'object' | 'array' | 'Buffer';
interface ifTableDsc {
    name?: string,
    type: TableValueType,
    comment?: string,
    isPrimary?: boolean,
}

export class Sqlit3DB {
    ready: boolean = false;
    db: sqlite3.Database;


    private static _table_infos_: Map<string, {
        keys: string[], types: ifTableDsc[], maptypes: any
    }> = new Map<string, {
        keys: string[], types: ifTableDsc[], maptypes: any
    }>();
    static get tables() {
        return this._table_infos_;
    }

    /**
     * 注册sql表格结构
     * @param tableName 
     * @param createSql 
     */
    static registTable(tableName: string, createSql: ifTableDsc[] | { [x: string]: ifTableDsc | TableValueType }) {
        if (this._table_infos_.has(tableName)) return;

        let obj = {};

        if (createSql instanceof Array) {
            let keys = [];
            for (let i = 0; i < createSql.length; i++) {
                obj[createSql[i].name] = createSql[i];
                keys.push(createSql[i].name);
            }

            this._table_infos_.set(tableName, { keys: keys, types: createSql, maptypes: obj });
        }
        else {
            let keys = [];
            let arr: ifTableDsc[] = [];
            for (let k in createSql) {
                keys.push(k);
                let v = createSql[k];
                if (typeof v != 'string')
                    v['name'] = k;
                else
                    v = { name: k, type: v };

                arr.push(v)
                obj[v.name] = v;
            }
            this._table_infos_.set(tableName, { keys: keys, types: arr, maptypes: obj });
        }
    }

    /**
     * 从sql语句中解析表格数据
     * @param create_sql 
     */
    static parseTable(create_sql: string) {
        let ct_sql = create_sql.toLocaleLowerCase();
        ct_sql = ct_sql.replace(/\r/g, '')   // 去掉 \r
        ct_sql = ct_sql.replace(/\n/g, '')  // 去掉 \n
        ct_sql = ct_sql.replace(/"/g, '')   // 去掉 "
        ct_sql = ct_sql.replace('create table', '').trim();

        let idx = ct_sql.indexOf(' ');
        let tname = ct_sql.substr(0, idx);
        let arrays = ct_sql.slice(ct_sql.indexOf('(') + 1, ct_sql.lastIndexOf(')')).split(',');

        let out: { name: string, des: Object } = { name: tname, des: {} };

        for (let i = 0; i < arrays.length; i++) {
            let a_infos = arrays[i].trim().split(' ');
            let cname = a_infos[0];
            let ctype = a_infos[1];
            out.des[cname] = ctype;
        }

        return out;
    }

    static savetype2value(type: TableValueType, value: any) {
        switch (type) {
            case 'string': return value;
            case 'object':
            case 'array': return JSON.stringify(value);
            case 'number': return value;
            case 'Buffer': return value.toString('base64');
            default: return value;
        }
    }

    static loadtype2value(type: TableValueType, value: any) {
        switch (type) {
            case 'string': return value;
            case 'object':
            case 'array': return JSON.parse(value);
            case 'number': return parseInt(value);
            case 'Buffer': return Buffer.from(value, 'base64');
            default: return value;
        }
    }

    static table_sql(tableName: string) {
        let sql = '';

        let tb = this._table_infos_.get(tableName);
        if (!tb) return sql;

        sql = `create table ${tableName} `

        let tks = [];
        let main_kays = [];
        for (let i = 0; i < tb.types.length; i++) {
            let tr = tb.types[i];

            let cttype = '';
            switch (tr.type) {
                case 'array': cttype = 'ntext'; break;
                case 'object': cttype = 'ntext'; break;
                case 'Buffer': cttype = 'text'; break;
                case 'number': cttype = 'real'; break;
                case 'string': cttype = 'ntext'; break;

            }
            tks.push(`${tr.name} ${cttype}`);

            if (tr.isPrimary) {
                main_kays.push(tr.name);
            }
        }
        main_kays.length && tks.push(`PRIMARY KEY (` + main_kays.join(' , ') + ')')
        sql += `(${tks.join(',')})`;
        return sql;
    }

    /**
     * 返回 A 表能存储 B 表数据需要增加的字段 的sql语句
     * @param sqlA 
     * @param sqlB 
     */
    static table_alter(sqlA: string, sqlB: string) {
        let alter_str = 'ALTER TABLE ';

        let tableA = this.parseTable(sqlA);
        let tableB = this.parseTable(sqlB);

        let alter = [];
        alter_str += tableA.name + ' '
        for (let key in tableB.des) {
            // 检查一下是否有新的表内容
            if (tableA.des.hasOwnProperty(key)) {
                if (tableA.des[key] != tableB.des[key]) {
                    alter.push(`MODIFY COLUMN ${key} ${tableB.des[key]}`);
                }
            }
            else {
                alter.push(`ADD COLUMN ${key} ${tableB.des[key]}`);
            }
        }

        if (alter.length == 0) return '';

        alter_str += alter.join(',');
        return alter_str;
    }

    private static _inst_: Sqlit3DB;
    static get inst() {
        if (!this._inst_) this._inst_ = new Sqlit3DB();
        return this._inst_;
    }


    private constructor() {

    }
    public async init(path: string) {
        // var sqlite = require('sqlite3').verbose();
        function create(p: string) {
            return new Promise<sqlite3.Database>(function (resolve, reject) {
                let db = new (sqlite3.verbose()).Database(p, function (err) {
                    if (err) reject(err);
                    resolve(db);
                });
            })
        }

        let dir = parse(path);
        if (!existsSync(dir.dir)) mkdirSync(dir.dir);

        this.db = await create(path)
        await this.tablecheck();
        this.ready = true;
        return true;
    }

    private db_serialize() {
        return new Promise((resolve) => {
            this.db.serialize(resolve)
        })
    }

    private db_all(sql: string) {
        return new Promise<any[]>((resolve, reject) => {
            this.db.all(sql, function (err, row) {
                if (err) reject(err);
                else resolve(row)
            })
        })
    }

    private db_exec(sql: string) {
        return new Promise<void>((resolve, reject) => {
            this.db.exec(sql, function (err) {
                if (err) reject(err);
                else resolve()
            })
        })
    }

    private async tablecheck() {
        await this.db_serialize();
        //where type='table'
        let row = await this.db_all("select * from sqlite_master ")
        let tableMaps = new Map<string, string>();

        for (let i = 0; i < row.length; i++) {
            let rt = row[i];
            if (rt.type == 'table') {
                tableMaps.set(rt.tbl_name, rt.sql);
                Sqlit3DB.parseTable(rt.sql);
            }
            else {
                // console.log(rt);
            }
        }

        let sql_list = [];
        let tbs = Sqlit3DB.tables;
        tbs.forEach(function (v, k) {
            let csql = Sqlit3DB.table_sql(k);
            let tsql = tableMaps.get(k);
            if (tsql == csql) {
                // sql 一致
            }
            else if (!tsql) {
                // 表不存在
                sql_list.push(csql);
            }
            else {
                // 表结构不同
                let msql = Sqlit3DB.table_alter(tsql, csql);
                msql && sql_list.push(msql);
            }
        })


        for (let i = 0; i < sql_list.length; i++) {
            let sql = sql_list[i];
            await this.db_exec(sql);
        }

        return true;
    }

    //-------------------------处理数据-------------------------------//

    private _subLine_(sub_find: Object, type?: ifTableDsc): string[] {
        let out_str: string[] = [];
        for (let k in sub_find) {
            let fv = sub_find[k];

            let sub_out = [];

            if (typeof fv == 'object') {
                sub_out = this._subLine_(fv);
            }
            else {
                if (type && type.type == 'number') {
                    sub_out = [fv];
                }
                else {
                    sub_out = ["'" + fv.toString() + "'"];
                }
            }

            switch (k) {
                case '$or': out_str.push('(' + sub_out.join(' or ') + ')'); break;
                case '$and': out_str.push('(' + sub_out.join(' and ') + ')'); break;
                case '$ne': out_str.push('!=' + sub_out[0]); break;
                case '$gt': out_str.push(sub_out[0]); break;
                case '$gte': out_str.push('>=' + sub_out[0]); break;
                case '$lt': out_str.push('<' + sub_out[0]); break;
                case '$lte': out_str.push('<=' + sub_out[0]); break;
                case '$in': out_str.push('in (' + sub_out.join(' , ') + ')'); break;
                default:
                    if (sub_find instanceof Array) {
                        out_str.push(sub_out.join(' , '));
                    }
                    else {
                        out_str.push(k + '=' + sub_out.join(' , '));
                    }
                    break;
            }
        }
        return out_str;
    }

    public async selectInfo(table: string, find: Object) {
        let tabRes = Sqlit3DB._table_infos_.get(table);
        if (!tabRes) return [];

        let sql = 'select * from ' + table + ' where ' + this._subLine_(find, null).join(' and ');

        let row = await this.db_all(sql);
        let t_res = Sqlit3DB._table_infos_.get(table).maptypes;
        if (row) {
            for (let i = 0; i < row.length; i++) {
                let r_row = row[i];
                for (let key in r_row) {
                    let k_tp = t_res[key];
                    r_row[key] = Sqlit3DB.loadtype2value(k_tp ? k_tp.type : 'string', r_row[key]);
                }
            }
        }
        // row && console.log(JSON.stringify(row));
        return row;
    }

    public async updateInfo(table: string, find: Object, value: Object) {
        let tabRes = Sqlit3DB._table_infos_.get(table);
        if (!tabRes) return false;

        let setList: string[] = [];
        for (let k in value) {
            let cfg = tabRes.maptypes[k];
            let v = Sqlit3DB.savetype2value(cfg ? cfg.type : 'string', value[k]);

            setList.push(k + '=' + JSON.stringify(v))
        }

        let sql = `update ${table} set ${setList.join(',')} where ${this._subLine_(find, null).join(' and ')}`;
        await this.db_exec(sql)
        return true;
    }


    public async insertInfo(table: string, add: Object) {
        let tabRes = Sqlit3DB._table_infos_.get(table);
        if (!tabRes) return false;

        let v_sql = [];
        let k_sql = [];
        for (let k in add) {
            k_sql.push(k);
            let cfg = tabRes.maptypes[k];
            let v = Sqlit3DB.savetype2value(cfg ? cfg.type : 'string', add[k]);

            v_sql.push(JSON.stringify(v));
        }

        let sql = 'insert into ' + table + ' (' + k_sql.join(',') + ')values(' + v_sql.join(',') + ')';
        await this.db_exec(sql)
        return true;
    }

    public async deleteInfo(table: string, find: Object) {
        let tabRes = Sqlit3DB._table_infos_.get(table);
        if (!tabRes) return [];

        let sql = 'delete ' + table + ' where ' + this._subLine_(find, null).join(' and ');

        await this.db_exec(sql)
        // row && console.log(JSON.stringify(row));
        return true;
    }

    public rawExec(sql: string) {
        return this.db_exec(sql);
    }

    public rawQuery(sql: string) {
        return this.db_all(sql);
    }
}


